/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import com.caracol.db.CaracolQueryGenerator;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

/**
 * This class is used to represent the studys and the
 * CRUD operations.
 * 
 * @author Roque Rueda
 */
public class GradoAcademico {

    private static CaracolQueryGenerator caracolQuery = new CaracolQueryGenerator();
    private int id;
    private String descripcion;
    
    /**
     * Name of the id colum in the databaase
     */
    public static final String ID = "id";
    
    /**
     * Name of the descripcion colum in the database
     */
    public static final String DESCRIPCION = "descripcion";
    
    /**
     * Name of the table in the database
     */
    public static final String TABLE = "grado_academico";

    /**
     * Creates an instace with the default values
     */
    public GradoAcademico() {
        this(0, "");
    }

    /**
     * Creates an instace with the given values
     * @param id this is the identifier of this grado academico
     * @param descripcion description of this grado academico
     */
    private GradoAcademico(int id, String descripcion) {
        this.id = id;
        this.descripcion = descripcion;
    }
    
    /**
     * Creates an instace with the given values
     * @param descripcion description of this grado academico
     */
    public GradoAcademico(String descripcion){
        this(0, descripcion);
    }
    
    /**
     * Gets a grado academico from the database by his given id
     * @param id the id of the grado academico
     * @return the grado academico if its find in the dabase otherwise false
     * @throws Exception if some exception happends this is throw again
     */
    public static GradoAcademico getGradoAcademico(int id) throws Exception{
        StringBuilder sb = new StringBuilder("SELECT " + ID + ",");
        sb.append(DESCRIPCION + " FROM ");
        sb.append(TABLE + " WHERE " + ID + " = ").append(id);
        ArrayList<Object> resultset = new ArrayList(caracolQuery.executeQuery(sb.toString()));
        if(resultset != null && resultset.size() > 0){
            Vector<Object> row = (Vector<Object>) resultset.get(0);
            String descripcion = row.elementAt(1).toString();
            GradoAcademico ga = new GradoAcademico(id, descripcion);
            return ga;
        }
        return null;
    }
    
    /***
     * Save the values of this instance in the database
     * @return true if it's save or if this is already save otherwise false
     * @throws Exception if exception happends this is throw
     */
    public boolean save() throws Exception{
        if(getId() > 0){
            return true;
        }
        StringBuilder sb = new StringBuilder("INSERT INTO ");
        sb.append(TABLE);
        sb.append("(");
        sb.append(DESCRIPCION);
        sb.append(") VALUES (");
        sb.append("\'");
        sb.append(getDescripcion());
        sb.append("\'");
        sb.append(");");
        ArrayList<Object> dataset = null;
        int rowsAffected  = caracolQuery.executeCommandLeaveOpenConnection(sb.toString());
        if(rowsAffected > 0){
            sb = new StringBuilder("SELECT currval(\'sqgrado_academico\');");
            dataset = new ArrayList<Object>(caracolQuery.executeQuery(sb.toString()));
        }
        if (dataset != null && dataset.size()>0) {
            Vector<Object> row = (Vector<Object>) dataset.get(0);
            Double tem = Double.parseDouble(row.elementAt(0).toString());
            this.id = tem.intValue();
            return true;
        }
        return false;
    }
    
    /**
     * Get all the grado academico from the database
     * @return a list of grado academico
     */
    public static List<GradoAcademico> getAll() throws Exception{
        StringBuilder sb = new StringBuilder("SELECT " + ID + ",");
        sb.append(DESCRIPCION + " FROM ");
        sb.append(TABLE);
        ArrayList<GradoAcademico> result = new ArrayList<GradoAcademico>();
        ArrayList<Object> resultset = new ArrayList(caracolQuery.executeQuery(sb.toString()));
        if(resultset != null && resultset.size() > 0){
            for(Object obj : resultset){
                Vector<Object> row = (Vector<Object>) obj;
                int id = new Double(row.elementAt(0).toString()).intValue();
                String descripcion = row.elementAt(1).toString();
                GradoAcademico ga = new GradoAcademico(id, descripcion);
                result.add(ga);
            }
        }
        return result;
    }
    
    /**
     * Updates the content of the database witch contains the instace ID 
     * with the values of this object
     * 
     * @return true if the operation is commited otherwise false
     * @throws Exception if some exception happend this is throwed again
     */
    public boolean update() throws Exception{
        StringBuilder sb = new StringBuilder("UPDATE " + TABLE + " SET ");
        sb.append(DESCRIPCION + " = \'");
        sb.append(getDescripcion());
        sb.append("\' WHERE ID = ");
        sb.append(getId());
        
        int rowsAffected = caracolQuery.executeCommand(sb.toString());
        
        return rowsAffected > 0;
    }
    
    /**
     * Deletes the given grado academico it this is already deleted it 
     * returns true, this is evaluated checking it's id and compare if it's
     * greater than 0
     * 
     * @return true if the operation is realized with no error otherwise false
     * @throws Exception throws the exception if happend
     */
    public boolean delete() throws Exception{
        if (id <= 0) {
            return true;
        }
        
        StringBuilder sb = new StringBuilder("DELETE FROM " + TABLE + " ");
        sb.append(" WHERE id = ");
        sb.append(getId());
        
        int rowsAffected = caracolQuery.executeCommand(sb.toString());
        
        if(rowsAffected > 0 ) id = 0;
        
        return rowsAffected > 0;
    }

    /**
     * @return the id
     */
    public int getId() {
        return id;
    }

    /**
     * @return the descripcion
     */
    public String getDescripcion() {
        return descripcion;
    }

    /**
     * @param descripcion the descripcion to set
     */
    public void setDescripcion(String descripcion) {
        this.descripcion = descripcion;
    }
    
    @Override
    public String toString(){
        return descripcion;
    }
}
